use crate::error::{new_err_info, AppErr, SqlxErr, EC_NO_IMAGE, EC_PRODUCT_IS_EXISTS};
use serde::Serialize;
use sqlx::Row;
use sqlx::{Executor, SqliteConnection};

use super::get_pool;

const CREATE_SQL: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_product2 (
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        name TEXT NOT NULL, 
        price INTEGER NOT NULL,
        image_id INTEGER NOT NULL, 
        UNIQUE(name)
    )
"#;

const CREATE_SQL_IMG: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_img (
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        body BLOB NOT NULL
    )
"#;

#[derive(Debug, Serialize)]
pub struct Product2 {
    id: i64,
    name: String,
    price: u32,
    image_id: i64,
}

pub async fn init() -> Result<(), SqlxErr> {
    let pool = get_pool();
    pool.execute(CREATE_SQL).await?;
    pool.execute(CREATE_SQL_IMG).await?;
    Ok(())
}

async fn is_exists(conn: &mut SqliteConnection, name: &str) -> Result<Option<i64>, SqlxErr> {
    let row = sqlx::query(
        r#"
        SELECT id FROM tb_product2 WHERE name = ? LIMIT 1
    "#,
    )
    .bind(name)
    .fetch_optional(conn)
    .await?
    .and_then(|row| row.get(0));
    Ok(row)
}

async fn create_image(conn: &mut SqliteConnection, image: &[u8]) -> Result<i64, SqlxErr> {
    let ret = sqlx::query(
        r#"
        INSERT INTO tb_img (body) VALUES (?)
    "#,
    )
    .bind(image)
    .execute(conn)
    .await?;
    Ok(ret.last_insert_rowid())
}

async fn delete_image2(conn: &mut SqliteConnection, id: i64) -> Result<(), SqlxErr> {
    sqlx::query(
        r#"DELETE FROM tb_img WHERE id = (
        SELECT image_id FROM tb_product2 WHERE id = ? LIMIT 1
    )"#,
    )
    .bind(id)
    .execute(conn)
    .await?;
    Ok(())
}

async fn create_impl(
    conn: &mut SqliteConnection,
    name: &str,
    price: u32,
    image_id: i64,
) -> Result<i64, AppErr> {
    let ret = sqlx::query(
        r#"
        INSERT INTO tb_product2 (name, price, image_id) VALUES (?, ?, ?)
   "#,
    )
    .bind(name)
    .bind(price)
    .bind(image_id)
    .execute(conn)
    .await?;
    Ok(ret.last_insert_rowid())
}

pub async fn create(name: &str, price: u32, image: &[u8]) -> Result<i64, AppErr> {
    let mut tx = get_pool().begin().await?;
    if is_exists(&mut tx, name).await?.is_some() {
        return Err(new_err_info(EC_PRODUCT_IS_EXISTS, "商品名称重复"));
    }
    let image_id = create_image(&mut tx, image).await?;
    let id = create_impl(&mut tx, name, price, image_id).await?;

    tx.commit().await?;

    Ok(id)
}

pub async fn edit(id: i64, name: &str, price: u32, image: Option<&[u8]>) -> Result<(), SqlxErr> {
    let mut tx = get_pool().begin().await?;

    if let Some(image) = image {
        delete_image2(&mut tx, id).await?;
        let image_id = create_image(&mut tx, image).await?;
        sqlx::query(
            r#"
            UPDATE tb_product2 SET name = ?, price = ?, image_id = ? WHERE id = ?
        "#,
        )
        .bind(name)
        .bind(price)
        .bind(image_id)
        .bind(id)
        .execute(&mut *tx)
        .await?;
    } else {
        sqlx::query(
            r#"
            UPDATE tb_product2 SET name = ?, price = ? WHERE id = ?
        "#,
        )
        .bind(name)
        .bind(price)
        .bind(id)
        .execute(&mut *tx)
        .await?;
    }

    tx.commit().await?;

    Ok(())
}

pub async fn delete(id: i64) -> Result<(), SqlxErr> {
    sqlx::query(r#"DELETE FROM tb_product2 WHERE id = ?"#)
        .bind(id)
        .execute(get_pool())
        .await?;
    Ok(())
}

pub async fn query() -> Result<Box<[Product2]>, SqlxErr> {
    let rows = sqlx::query(
        r#"
        SELECT id, name, price, image_id FROM tb_product2
    "#,
    )
    .fetch_all(get_pool())
    .await?;

    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let product = Product2 {
            id: row.get(0),
            name: row.get(1),
            price: row.get(2),
            image_id: row.get(3),
        };
        vec.push(product);
    }
    Ok(vec.into_boxed_slice())
}

pub async fn get_image(image_id: i64) -> Result<Box<[u8]>, AppErr> {
    let row = sqlx::query(
        r#"
        SELECT body FROM tb_img WHERE id = ?
    "#,
    )
    .bind(image_id)
    .fetch_optional(get_pool())
    .await?
    .ok_or(new_err_info(EC_NO_IMAGE, "没有图片"))?;

    Ok(row.get(0))
}
